<?php

namespace common\models;

use common\models\common\Log;
use common\models\operatelogs\Logs;
use common\models\Tab;
use common\models\Visit;
use common\models\Subject;
use Yii;
use yii\helpers\ArrayHelper;
use yii\behaviors\TimestampBehavior;
use common\models\varbsdvcheck\VarbSdvCheck;

/**
 * This is the model class for table "tab_check".
 *
 * @author        Bran <lin.yang@ht-med.com>
 * @copyright (c) 2015, HT-MED
 * @package       common.models
 * @since         version 1.0
 *
 * @property integer $id
 * @property integer $seq
 * @property integer $tab_id
 * @property integer $visit_id
 * @property integer $status
 * @property integer $sdv
 * @property integer $sdv_user
 * @property integer $sdv_date
 * @property integer $qa
 * @property integer $qa_user
 * @property integer $qa_date
 * @property integer $check
 * @property integer $check_user
 * @property integer $check_date
 * @property integer $created_at
 * @property integer $updated_at
 */
class TabCheck extends \yii\db\ActiveRecord
{
    public $_note;//删除、恢复时填写备注
    const STATUS_UNSTARTED = 0;//未开始
    const STATUS_STARTED = 1;//进行中
    const STATUS_COMPLETED = 2;//已完成
    const STATUS_SKIPED = 3;//已跳过
    const STATUS_SIGNED = 4;//已签名
    const STATUS_LOCKED = 5;//已锁定
    const STATUS_DEBLOCKED = 6;//已解锁
    
    const STATUS_SDVED = 1;//已做SDV
    const STATUS_NOSDV = 0;//未做SDV
    
    const STATUS_CHECKED = 1;//已核查
    const STATUS_UNCHECK = 0;//未核查
    
    const STATUS_QA = 2;
    const STATUS_NOQA = 1;
    
    const DELETE_N = 1;
    const DELETE_Y = 2;
    
    public function getDeletes()
    {
        return [
            self::DELETE_N => '有效',
            self::DELETE_Y => '无效',
        ];
    }
    
    
    /**
     * 所有数据表状态的的getter方法
     *
     * @return array $statuses
     */
    public function getStatuses($intStatus = null)
    {
        $statuses = [
            self::STATUS_UNSTARTED => Yii::t('tabcheck', 'uninitiated'),
            self::STATUS_STARTED => Yii::t('tabcheck', 'pending'),
            self::STATUS_COMPLETED => Yii::t('tabcheck', 'completed'),
            self::STATUS_SKIPED => Yii::t('tabcheck', 'skiped'),
            self::STATUS_SIGNED => Yii::t('tabcheck', 'signed'),
            self::STATUS_LOCKED => Yii::t('tabcheck', 'locked'),
            //            self::STATUS_DEBLOCKED => Yii::t('tabcheck', 'deblocked'),
        ];
        if ($intStatus !== null && isset($statuses[$intStatus])) {
            $statuses = $statuses[$intStatus];
        }
        
        return $statuses;
        
    }
    
    /**
     * 所有数据表sdv状态的getter方法
     *
     * @return array $sdvs
     */
    public function getsdvs()
    {
        return [
            self::STATUS_SDVED => "已做SDV",
            self::STATUS_NOSDV => "未做SDV",
        ];
    }
    
    /**
     * 所有数据表机构质控状态的getter方法
     *
     * @return array $qas
     */
    public function getQAs()
    {
        return [
            self::STATUS_QA => "机构已质控",
            self::STATUS_NOQA => "机构未质控",
        ];
    }
    
    /**
     * 对数据表操作的getter方法
     *
     * @return array $actions
     */
    public static function getActions()
    {
        return [
            self::STATUS_COMPLETED => Yii::t('tabcheck', 'To Complete'),
            self::STATUS_SKIPED => Yii::t('tabcheck', 'To Skip'),
            self::STATUS_SIGNED => Yii::t('tabcheck', 'To Sign'),
            self::STATUS_LOCKED => Yii::t('tabcheck', 'To Lock'),
            //            self::STATUS_DEBLOCKED => Yii::t('tabcheck', 'To Deblock'),
        ];
    }
    
    /**
     * 数据表状态所对应图标的getter方法
     *
     * @return array $icons
     */
    public function getIcons()
    {
        return [
            self::STATUS_UNSTARTED => 'unchecked',
            self::STATUS_STARTED => 'tasks',
            self::STATUS_COMPLETED => 'saved',
            self::STATUS_SKIPED => 'fast-forward',
            self::STATUS_SIGNED => 'star',
            self::STATUS_LOCKED => 'lock',
            //            self::STATUS_DEBLOCKED => 'off',
        ];
    }
    
    /**
     * 数据表状态所对应颜色的getter方法
     *
     * @return array $colors
     */
    public function getColors()
    {
        return [
            self::STATUS_UNSTARTED => 'gray',
            self::STATUS_STARTED => '#19C3FB',
            self::STATUS_COMPLETED => 'blue',
            self::STATUS_SKIPED => '#AF8704',
            self::STATUS_SIGNED => '#07690B',
            self::STATUS_LOCKED => 'green',
            //            self::STATUS_DEBLOCKED => '#FF34B3',
        ];
    }
    
    /**
     * 数据表状态所对应title getter方法
     *
     * @return array $colors
     */
    public function getTitles()
    {
        return [
            self::STATUS_UNSTARTED => '未开始',
            self::STATUS_STARTED => '进行中',
            self::STATUS_COMPLETED => '已完成',
            self::STATUS_SKIPED => '已跳过',
            self::STATUS_SIGNED => '已签名',
            self::STATUS_LOCKED => '已锁定',
            //            self::STATUS_DEBLOCKED => '已解锁',
        ];
    }
    
    public function getIsValue()
    {
        $value = Values::findOne(['visit_id' => $this->visit_id, 'tab_id' => $this->tab_id, 'seq' => $this->seq]);
        return $value ? true : $this->status > self::STATUS_COMPLETED ? true : false;
    }
    
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'tab_check';
    }
    
    /**
     * @inheritdoc
     */
    public function behaviors()
    {
        return [
            TimestampBehavior::className(),
        ];
    }
    
    /**
     * AR关联，获取Visit对象的getter方法
     *
     * @return Visit $visit
     */
    public function getVisit()
    {
        return $this->hasOne(Visit::className(), ['id' => 'visit_id']);
    }
    
    /**
     * AR关联，获取Tab对象的getter方法
     *
     * @return Tab $tab
     */
    public function getTab()
    {
        return $this->hasOne(Tab::className(), ['id' => 'tab_id']);
    }
    
    /**
     * AR关联，获取Subject对象的getter方法
     *
     * @return SUbject $subject
     */
    public function getSubj()
    {
        return $this->hasOne(Subject::className(), ['seq' => 'seq']);
    }
    
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['seq', 'tab_id', 'visit_id', 'status'], 'required'],
            [['seq', 'tab_id', 'visit_id', 'status', 'sdv', 'sdv_user', 'sdv_date', 'check', 'check_user', 'check_date', 'created_at', 'updated_at', 'creator', 'qa', 'qa_user', 'qa_date'], 'integer'],
            [['sdv'], 'default', 'value' => self::STATUS_NOSDV],
            [['qa'], 'default', 'value' => self::STATUS_NOQA],
            [['check'], 'default', 'value' => self::STATUS_UNCHECK],
            ['creator', 'default', 'value' => getUserId()],
            [['seq', 'tab_id', 'visit_id'], 'checkProject'],
            [['_note'], 'safe'],
        ];
    }
    
    public function checkProject($attribute, $params)
    {
        $tab = Tab::findOne($this->tab_id);
        $visit = Visit::findOne($this->visit_id);
        $seq = Subject::findOne($this->seq);
        if (!$tab || !$visit || !$seq) {
            $this->addError($attribute, "受试者，访视，数据表不匹配！请勿违规操作.");
            return false;
        }
        if (!($seq->project_id == $visit->project_id && $seq->project_id == $tab->project_id)) {
            $this->addError($attribute, "受试者，访视，数据表不匹配！请勿违规操作.");
            return false;
        }
    }
    
    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'id' => Yii::t('tabcheck', 'ID'),
            'seq' => Yii::t('tabcheck', 'Seq'),
            'tab_id' => Yii::t('tabcheck', 'Tab ID'),
            'visit_id' => Yii::t('tabcheck', 'Visit ID'),
            'status' => Yii::t('tabcheck', 'Status'),
            'sdv' => Yii::t('tabcheck', 'sdv'),
            'sdv_user' => Yii::t('tabcheck', 'Sdv User'),
            'sdv_date' => Yii::t('tabcheck', 'Sdv Date'),
            'check' => Yii::t('tabcheck', 'Check'),
            'check_user' => Yii::t('tabcheck', 'Check User'),
            'check_date' => Yii::t('tabcheck', 'Check Date'),
            'qa' => '质控',
            'qa_user' => '质控员',
            'qa_date' => '质控时间',
            'is_delete' => Yii::t('tabcheck', 'Delete Date'),
        ];
    }
    
    public function fields()
    {
        return [
            'seq',
            'domain' => function ($data) {
                return $data->tab->tab_content;
            },
            'visit_seq' => function ($data) {
                return $data->visit->visit_seq;
            },
            'visit_name' => function ($data) {
                return $data->visit->visit_name;
            },
            'sdv',
            'sdv_time' => function ($data) {
                return Yii::$app->formatter->asDatetime($data->sdv_date, 'yyyy-MM-dd HH:mm');
            },
            'updated_at',
        ];
    }
//
//    public function deleteRecord($no)
//    {
//        $db = Yii::$app->db;
//        $query = "SELECT `id` FROM `query` WHERE `seq` = " . $this->seq . " AND visit_id = " . $this->visit_id . " AND varb_id IN (SELECT id FROM varb WHERE tab_id = " . $this->tab_id . ") AND `no` = $no AND status IN (1,2,4)";
//        $queryData = $db->createCommand($query)->queryOne();
//        if ($queryData) {
//            return '该数据下有未解决质疑，不可删除';
//        }
//        $delSql = "
//					UPDATE `values`
//          SET `value` = NULL, deleted=0
//					WHERE seq = " . $this->seq . " AND visit_id = " . $this->visit_id . "
//					 AND varb_id IN (SELECT id FROM varb WHERE tab_id = " . $this->tab_id . ") AND `no` = $no";
//
//        $logSql = "INSERT INTO audit_trail(`project_id`,`values_id`,`seq`,`new_value`,`old_value`,`modify_reason`,`user_id`,`modify_time`,`trail_type`)
//						SELECT t.project_id, t.id," . $this->seq . ", NULL,`value`,'删除整条记录'," . getUserId() . "," . time() . ",3
//						FROM `values` t
//	 					LEFT JOIN varb v
//	 					ON t.varb_id = v.id
//	 					WHERE seq = " . $this->seq . " AND visit_id = " . $this->visit_id . " and t.project_id=" . getSession('defaultPid') . "
//	 					 AND t.tab_id = " . $this->tab_id . " AND `no` = $no";
//        // $updateSql = "
//        // 			UPDATE `values`
//        //         SET no = no - 1
//        //         WHERE seq = ".$this->seq." AND visit_id = ".$this->visit_id."
//        // 			 AND varb_id IN (SELECT id FROM varb WHERE tab_id = ".$this->tab_id.") AND `no` > $no";
//
//        $transaction = $db->beginTransaction();
//
//        try {
//            $db->createCommand($logSql)->execute();
//            $db->createCommand($delSql)->execute();
//            $transaction->commit();
//        } catch (\Exception $e) {
//            $transaction->rollBack();
//            throw $e;
//        }
//        return true;
//    }
    
    /**
     * Created by PhpStorm.
     * 多记录删除-变更状态以及插入痕迹
     * @param $post 提交的参数
     * @return array
     * @author syy
     * @date 2021-05-11
     */
    public function deleteRecord($post)
    {
        $db = Yii::$app->db;
        $transaction = $db->beginTransaction();
        //判断质疑情况
        $res = $this->checkQuery($post);
        if($res['code'] != 200) return $res;
        //判断参数
        if(!isset($post['_note']) || !isset($post['no'])) return arrayReturn(-1002, '提交数据为空.');
        if(!$post['no']) return arrayReturn(-1002, '提交数据为空.');
        if(!$post['_note']) return arrayReturn(-1003, '备注不能为空.');
        
        $query = Values::find()->where([
            'seq' => $this->seq,
            'visit_id' => $this->visit_id,
            'tab_id' => $this->tab_id,
            'no' => $post['no'], 'deleted' => Values::DELETED_N
        ]);
        //如果记录存在就作批量删除操作
        if($query->count()){
            $delSql = "UPDATE `values` SET deleted=1
					WHERE seq = " . $this->seq . " AND visit_id = " . $this->visit_id . "
					 AND varb_id IN (SELECT id FROM varb WHERE tab_id = " . $this->tab_id . ") AND `no` = ". $post['no'];
            
            $logSql = "INSERT INTO audit_trail(`project_id`,`values_id`,`seq`,`new_value`,`old_value`,`modify_reason`,`user_id`,`modify_time`,`trail_type`)
						SELECT t.project_id, t.id," . $this->seq . ", `value`, `value`,'{$post["_note"]}'," . getUserId() . "," . time() . ",3
						FROM `values` t
	 					LEFT JOIN varb v
	 					ON t.varb_id = v.id
	 					WHERE seq = " . $this->seq . " AND visit_id = " . $this->visit_id . " and t.project_id=" . getSession('defaultPid') . "
	 					 AND t.tab_id = " . $this->tab_id . " AND `no` = ".$post['no'];
            try {
                $res = $db->createCommand($delSql)->execute();
                $res1 = $db->createCommand($logSql)->execute();
                if($res && $res1){
                    $transaction->commit();
                    return arrayReturn(200, '操作成功.');
                }else{
                    $transaction->rollBack();
                    return arrayReturn(-1004, '操作失败.');
                }
            } catch (\Exception $e) {
                $transaction->rollBack();
                return arrayReturn(-1005, $e->getMessage());
            }
        }
        return arrayReturn(200, '操作成功.');
    }
    
    /**
     * Created by PhpStorm.
     * 多记录删除-判断是否存在质疑;
     * @param $post 提交的参数
     * @return array
     * @author syy
     * @date 2021-05-11
     */
    public function checkQuery($post){
        $db = Yii::$app->db;
        //判断质疑情况
        $query_status = Query::STATUS_NEW .','.Query::STATUS_UPDATED.','.Query::STATUS_REJECT;
        $query = "SELECT `id` FROM `query` WHERE `seq` = " . $this->seq . "
            AND visit_id = " . $this->visit_id . "
            AND varb_id IN (SELECT id FROM varb WHERE tab_id = " . $this->tab_id . ")
        AND `no` = ".$post['no']."
            AND status IN ($query_status)";
        $queryData = $db->createCommand($query)->queryOne();
        if ($queryData) return arrayReturn(-1001, '有质疑，不可做删除操作.');
        return arrayReturn(200, '无质疑，可操作.');
    }
    
    /**
     * 恢复被删除的变量值
     *
     * @param $id tab_check ID
     * @author syy
     * @date 2020-10-16
     */
    public function resetRecord($no, $note)
    {
        $db = Yii::$app->db;
        $transaction = $db->beginTransaction();
        $query = Values::find()->where(['seq' => $this->seq,
            'visit_id' => $this->visit_id, 'tab_id' => $this->tab_id, 'no' => $no, 'deleted' => Values::DELETED_Y
        ]);
        
        //如果记录存在就作批量恢复操作
        if($query->count()){
            $resetSql = "UPDATE `values` SET deleted=0
					WHERE seq = " . $this->seq . " AND visit_id = " . $this->visit_id . "
					 AND varb_id IN (SELECT id FROM varb WHERE tab_id = " . $this->tab_id . ") AND `no` = ". $no;
            
            $logSql = "INSERT INTO audit_trail(`project_id`, `values_id`,`new_value`,`old_value`,`modify_reason`,`user_id`,`modify_time`,`trail_type`)
    						SELECT t.project_id, t.id,`value`,`value`,'$note',".getUserId().",".time().", 4
    						FROM `values` t
    	 					LEFT JOIN varb v
    	 					ON t.varb_id = v.id
    	 					WHERE seq = ".$this->seq." AND visit_id = ".$this->visit_id ." and t.project_id=" . getSession('defaultPid') . "
    	 					 AND t.tab_id = ".$this->tab_id." AND `no` = ".$no;
            try {
                $res1 = $db->createCommand($resetSql)->execute();
                $res2 = $db->createCommand($logSql)->execute();
                if($res1 && $res2){
                    $transaction->commit();
                    return arrayReturn(200, '操作成功.');
                }else{
                    $transaction->rollBack();
                    return arrayReturn(-1001, '操作失败.');
                }
            } catch (\Exception $e) {
                $transaction->rollBack();
                return arrayReturn(1002, $e->getMessage());
            }
        }
        return arrayReturn(200, '操作成功.');
    }
    
    public function getChecksBySeq($seq, $defaultPid = null, $version_id = '')
    {
        if ($version_id == '') {
            //查询受试者所处版本号
            $subject = Subject::find()->where(['seq' => $seq])->select('version_id')->one();
            $version_id = $subject->version_id;
        }
        $entChecks = TabCheck::find()->where(['seq' => $seq, 'is_delete' => self::DELETE_N])->orderBy(['visit_id' => SORT_ASC, 'tab_id' => SORT_ASC])->all();
        $lst = [];
        foreach ($entChecks as $value) {
            $lst[$value->seq . '-' . $value->tab_id . '-' . $value->visit_id] = $value;
        }
        $tabs = Tab::find()->where(['project_id' => $defaultPid ? $defaultPid : getSession('defaultPid'), 'version_id' => $version_id])->all();
        $rows = [];
        foreach ($tabs as $tab) {
            $visits = explode(',', $tab->visits);
            foreach ($visits as $visit) {
                if (!isset($lst[$seq . '-' . $tab->id . '-' . $visit])) {
                    $check = new self;
                    $check->seq = $seq;
                    $check->tab_id = $tab->id;
                    $check->visit_id = $visit;
                    $check->status = self::STATUS_UNSTARTED;
                    $check->sdv = 0;
                    $check->check = 0;
                } else {
                    $check = $lst[$seq . '-' . $tab->id . '-' . $visit];
                }
                //获取访视visit_seq 用来派讯展示
                $visit_Model = Visit::findOne($visit);
                if(empty($visit_Model)){
                    $visit_seq = '';
                }else{
                    $visit_seq = $visit_Model->visit_seq;
                }
                
                $rows[sprintf("%'.20d", $visit_seq).'-'.sprintf("%'.20d", $visit) . '-' . sprintf("%'.20d", $tab->id)] = $check;
            }
        }
        ksort($rows);
        return $rows;
    }
    
    /**
     *  通过seq和访视id获取某个访视下数据域连接集合
     *
     */
    public static function tab_check_ids($visit_id, $seq)
    {
        $model = self::find()->where(['seq' => $seq, 'visit_id' => $visit_id, 'is_delete' => self::DELETE_N])->all();
        $ids = [];
        foreach ($model as $value) {
            $ids[] = $value['id'];
        }
        return $ids;
    }
    
    public static function addNew($seq, $vid, $tid)
    {
        if (!$seq || !$vid || !$tid) {
            return false;
        }
        static::addVisitAllTable($seq, $vid);
        if ($model = self::exists($seq, $vid, $tid)) {
            return $model;
        }
        return false;
    }
    
    /**
     * 插入访视下所有的table
     *
     * @author syy 2020.04.24
     */
    public static function addVisitAllTable($seq, $vid)
    {
        //查询受试者所处版本号
        $subject = Subject::find()->where(['seq' => $seq])->select('version_id')->one();
        $version_id = $subject->version_id;
        $tables = Tab::find()->where(['project_id' => getSession('defaultPid'), 'version_id' => $version_id])->orderBy('id asc')->all();
        
        $vals = [];
        foreach ($tables as $val) {
            
            if (self::exists($seq, $vid, $val->id) == false) {
                $visits = strpos($val->visits, ',') !== false ? $val->visits : $val->visits . ',';
                $array = explode(',', $visits);
                if (in_array($vid, $array)) {
                    $vals[] = [
                        $seq, $val->id, $vid, self::STATUS_UNSTARTED, 0, self::DELETE_N, self::STATUS_UNCHECK
                    ];
                }
            }
        }
        
        if (count($vals) == 0) {
            return true;
        }
        
        $key = ['seq', 'tab_id', 'visit_id', 'status', 'sdv', 'is_delete', 'check'];
        \Yii::$app->db->createCommand()->batchInsert(static::tableName(), $key, $vals)->execute();
        return true;
    }
    
    
    public static function exists($seq, $vid, $tid)
    {
        return self::find()->where([
            'seq' => $seq,
            'visit_id' => $vid,
            'tab_id' => $tid,
            'is_delete' => self::DELETE_N,
        ])->one();
    }
    
    public function getTabs($project = null)
    {
        if (!$project) {
            $project = Yii::$app->session->get('defaultPid');
        }
        $tabs = Tab::find()->where(['project_id' => $project])->groupBy('tab_name')->all();
        return ArrayHelper::map($tabs, 'id', 'tab_content');
    }
    
    public function getVisits()
    {
        $visits = Visit::find()->where(['project_id' => Yii::$app->session->get('defaultPid')])->all();
        return ArrayHelper::map($visits, 'id', 'visit_name');
    }
    
    public function visitDate($seq, $visit_id)
    {
        $one = SubjectVisitDate::findOne(['project_id' => Yii::$app->session->get('defaultPid'), 'seq' => $seq, 'visit_id' => $visit_id]);
        return empty($one) ? null : $one->frist_visit_date . '&nbsp;&nbsp;'. ((isset($one->center) && isset($one->rule)) ? $one->center->center . '&nbsp;&nbsp;'. $one->rule->title : '');
    }
    
    public function summaryQueryBySeq($project = null)
    {
        if (!$project) {
            $project = Yii::$app->session->get('defaultPid');
        }
        $query = self::find()
            ->select('center,s.subjid,s.screenid,s.name,t.seq,COUNT(*) as num,SUM(CASE WHEN t.sdv = 0 THEN 0 ELSE 1 END) sdv,s.status')
            ->from('tab_check t')
            ->leftJoin('subject s', 's.seq = t.seq')
            ->leftJoin('center_in_project c', 'c.id = s.cip_id')
            ->leftJoin('center r', 'r.id = c.center_id')
            ->andWhere(['s.project_id' => $project])
            ->andWhere(['>', 't.status', self::STATUS_STARTED])
            ->andWhere(['=', 't.is_delete', self::DELETE_N])
            ->andWhere(['<', 's.status', Subject::STATUS_INVALID])
            ->groupBy('t.seq')
            ->indexBy('seq');
        return $query;
    }
    
    public function summaryQABySeq()
    {
        $cip = \frontend\modules\approve\models\Helper::getCenterByHospRelation();
        $query = self::find()
            ->select('s.project_id,center,s.subjid,s.screenid,s.name,t.seq,COUNT(*) as num,SUM(CASE WHEN t.qa = 1 THEN 0 ELSE 1 END) sdv')
            ->from('tab_check t')
            ->leftJoin('subject s', 's.seq = t.seq')
            ->leftJoin('center_in_project c', 'c.id = s.cip_id')
            ->leftJoin('center r', 'r.id = c.center_id')
            ->andWhere(['>', 't.status', self::STATUS_STARTED])
            ->andWhere(['=', 't.is_delete', self::DELETE_N])
            ->andWhere(['in', 's.cip_id', $cip])
            ->groupBy('t.seq')
            ->indexBy('seq');
        return $query;
    }
    
    public function summaryByInvid($defaultPid = null)
    {
        $data = self::find()
            ->select('center,invid,center,COUNT(*) as num,SUM(CASE WHEN t.sdv = 0 THEN 0 ELSE 1 END) sdv')
            ->from('tab_check t')
            ->leftJoin('subject s', 's.seq = t.seq and s.status != 6')
            ->leftJoin('center_in_project c', 'c.id = s.cip_id')
            ->leftJoin('center r', 'r.id = c.center_id')
            ->andWhere(['s.project_id' => $defaultPid ? $defaultPid : Yii::$app->session->get('defaultPid')])
            ->andWhere(['>', 't.status', self::STATUS_STARTED])
            ->andWhere(['=', 't.is_delete', self::DELETE_N])
            ->groupBy('invid')
            ->createCommand()
            ->queryAll();
        return $data;
    }
    
    public function summaryQAByInvid($defaultPid = null)
    {
        $cip = \frontend\modules\approve\models\Helper::getCenterByHospRelation();
        $data = self::find()
            ->select('c.project_id,invid,center,COUNT(*) as num,SUM(CASE WHEN t.qa = 1 THEN 0 ELSE 1 END) sdv')
            ->from('tab_check t')
            ->leftJoin('subject s', 's.seq = t.seq')
            ->leftJoin('center_in_project c', 'c.id = s.cip_id')
            ->leftJoin('center r', 'r.id = c.center_id')
            ->andWhere(['>', 't.status', self::STATUS_STARTED])
            ->andWhere(['=', 't.is_delete', self::DELETE_N])
            ->andWhere(['in', 's.cip_id', $cip])
            ->groupBy('invid')
            ->createCommand()
            ->queryAll();
        return $data;
    }
    
    public function checkOldProject($id)
    {
        for ($i = 0; $i < 100; $i++) {
            $data = self::findOne(['id' => $id]);
            if ($data && $data->is_delete == 1) {
                if (isset($data->subj->project_id) && isset($data->tab->project_id) && isset($data->visit->project_id)) {
                    if ($data->subj->project_id != $data->tab->project_id || $data->subj->project_id != $data->visit->project_id) {
                        $sql = "update tab_check set is_delete = 2 where id = " . $id;
                        Yii::$app->db->createCommand($sql)->execute();
                        var_dump($data->errors);
                    } else {
                        echo $id . '正常';
                    }
                } else {
                    $sql = "update tab_check set is_delete = 2 where id = " . $id;
                    Yii::$app->db->createCommand($sql)->execute();
                    echo $id . 'seq异常<br/>';
                }
            } else {
                if ($data) {
                    echo $id . '已标记无效';
                } else {
                    echo $id . '数据不存在';
                }
            }
            $id++;
        }
        return $id;
    }
    
    /**
     * 数据域是否存在query
     *
     * @author syy
     * @date   2020.8.5
     */
    public static function hasNewQuery($tab_check_id, $seq, $visit_id, $projectId = 0)
    {
        $varb_ids = TabCheck::find()
            ->where(['tab_check.id' => $tab_check_id, 'seq' => $seq])
            ->leftJoin('varb v', 'tab_check.tab_id = v.tab_id')
            ->select('v.id')->asArray()->column();
        $where['project_id'] = $projectId ? $projectId : getSession('defaultPid');
        $andWhere = ['in', 'varb_id', $varb_ids];
        $where['visit_id'] = $visit_id;
        $where['seq'] = $seq;
        return \common\models\Helper::hasNewQuery($where, $andWhere);
    }
    
    /**
     * @param $seq    受试者唯一标识
     * @param $status 状态
     *
     * @return 根据条件获取数据域数量
     * @author syy
     * @date   2020-08-11 下午2:14
     */
    public static function getTabStatuses($seq)
    {
        $where['tab_check.seq'] = $seq;
        //获取所有数量
        $count = \common\models\Helper::getTabCount($where);
        $unstartedTabCount = self::getUnstartedTabCount($seq);
        $lockedTabCount = self::getLockendTabCount($seq);
        if ($unstartedTabCount == $count) {
            $statusInt = self::STATUS_UNSTARTED;
        } else {
            if ($lockedTabCount == $count) {
                $statusInt = self::STATUS_LOCKED;
            } else {
                $statusInt = self::STATUS_STARTED;
            }
        }
        
        return $statusInt;
        
    }
    
    /*
     * 获取sdv DM核查 数据域状态集合
     * sdv=1待SDV;sdv=0已SDV
     * checked=1待DM核查;checked=0已核查
     * status 未开始;1=进行中;2=进行中;5=已锁定
     * @author syy
     * @return array
     */
    public static function getTabAllStatus($seq)
    {
        $statusInt = self::getTabStatuses($seq);
        
        /*获取sdv状态*/
        if ($statusInt == TabCheck::STATUS_UNSTARTED) {
            $sdv = 1;
            $checked = 1;
        } else {
            $sdv = self::getTabSdvCheckStatus($seq, $param = 'sdv');
            $checked = self::getTabSdvCheckStatus($seq, $param = 'check');
        }
        return [
            'sdv' => $sdv,
            'checked' => $checked,
            'statusInt' => $statusInt,
        ];
    }
    
    /**
     * @param $seq       受试者唯一标识
     * @param $statusInt 状态
     *
     * @return 获取SDV、核查状态
     * @author syy
     * @date   2020-08-11
     */
    public static function getTabSdvCheckStatus($seq, $param = 'sdv')
    {
        if ($param == 'check') {
            $status = TabCheck::STATUS_UNCHECK;
        } else {
            $status = TabCheck::STATUS_NOSDV;
        }
        $sdvOrCheckStatus = TabCheck::find()->where(['seq' => $seq, 'is_delete' => TabCheck::DELETE_N, "$param" => $status])
            ->andWhere(['>', 'tab_check.status', TabCheck::STATUS_STARTED])->limit(1)->count();
        if (!$sdvOrCheckStatus) {
            $sdvOrCheckStatus = TabCheck::find()->where(['seq' => $seq, 'is_delete' => TabCheck::DELETE_N])
                ->andWhere(['>', 'tab_check.status', TabCheck::STATUS_STARTED])->limit(1)->count();
            if ($sdvOrCheckStatus) {
                /*第一种情况是都已经进行已sdv或者已核查*/
                $sdvOrCheckStatus = 0;
            } else {
                /*第二种情况是状态还都是<=1的；这种默认是待sdv或者待核查*/
                $sdvOrCheckStatus = 1;
            }
        }
        return $sdvOrCheckStatus;
    }
    
    /**
     * @param $seq 受试者唯一标识
     *
     * @return 获取状态
     * @author syy
     * @date   2020-08-11 下午2:14
     */
    public static function getUnstartedTabCount($seq)
    {
        $where['tab_check.seq'] = $seq;
        $where['status'] = self::STATUS_UNSTARTED;
        return \common\models\Helper::getTabCount($where);
    }
    
    /**
     * @param $seq 受试者唯一标识
     *
     * @return 获取状态为已锁定的数量
     * @author syy
     * @date   2020-08-11 下午2:14
     */
    public static function getLockendTabCount($seq)
    {
        $where['tab_check.seq'] = $seq;
        $where['status'] = self::STATUS_LOCKED;
        return \common\models\Helper::getTabCount($where);
    }
    
    //    /**
    //     * 更新数据域sdv、核查状态
    //     *
    //     * @param $id tab_check表id
    //     * @author syy
    //     * @date 2020-08-20
    //     * @return boolean
    //     */
    //    public static function updateSdvAndCheckStatus($id){
    //        $model = self::findOne(['id' => $id, 'is_delete' => self::DELETE_N]);
    //        if($model->sdv == self::STATUS_SDVED ){
    //            $model->sdv = self::STATUS_NOSDV;
    //        }
    //        if($model->check == self::STATUS_SDVED ){
    //            $model->check = self::STATUS_UNCHECK;
    //        }
    //        if($model->qa == self::STATUS_QA ){
    //            $model->qa = self::STATUS_NOQA;
    //        }
    //        if($model->save()){
    //            Logs::create($model);
    //            return true;
    //        }
    //        return false;
    //    }
    
    //查询某访视下的数据域并判断是否符合操作条件
    public function SelectCheckList($con, $project_id)
    {
        
        if (!isset($con['seq']) || !$con['seq']) {
            return responseError(301, '参数seq错误');
        }
        if (!isset($con['visit_id']) || !$con['visit_id']) {
            return responseError(301, '参数visit_id错误');
        }
        //查询受试者所处版本号
        $subject = Subject::find()->where(['seq' => $con['seq']])->select('version_id')->one();
        $version_id = $subject->version_id;
        
        $entChecks = TabCheck::find()->where(['seq' => $con['seq'], 'visit_id' => $con['visit_id'], 'is_delete' => self::DELETE_N])->all();
        $tab_list = Tab::find()->where(['project_id' => $project_id, 'version_id' => $version_id])->asArray()->all();
        if (empty($tab_list)) {
            return responseError(301, '数据域不存在');
        }
        foreach ($tab_list as $k => $v) {
            $visits = explode(',', $v['visits']);
            if (!in_array($con['visit_id'], $visits)) {
                unset($tab_list[$k]);
            }
        }
        foreach ($tab_list as $k => $v) {
            $tab_list[$k]['seq'] = $con['seq'];
            $tab_list[$k]['visit_id'] = $con['visit_id'];
            switch ($con['subject_check_type']) {
                case 'skip':       //是否跳过判断
                    $tab_list[$k]['is_opt'] = $this->batchJudgeSkip($entChecks, $v);
                    break;
                case 'sign':       //是否签名
                    $tab_list[$k]['is_opt'] = $this->batchJudgeSign($entChecks, $v);
                    break;
                case 'lock':       //是否锁定
                    $tab_list[$k]['is_opt'] = $this->batchJudgeLock($entChecks, $v);
                    break;
                case 'deblock':     //是否解锁
                    $tab_list[$k]['is_opt'] = $this->batchJudgeDeblock($entChecks, $v);
                    break;
                case 'sdv':       //是否SDV
                    $tab_list[$k]['is_opt'] = $this->batchJudgeSdv($entChecks, $v);
                    break;
                case 'check':       //是否核查
                    $tab_list[$k]['is_opt'] = $this->batchJudgeCheck($entChecks, $v);
                    break;
                default:
                    return responseError(301, '参数subject_check_type错误');
            }
            
        }
        if (empty($tab_list)) {
            return responseError(301, '数据域不存在');
        }
        return responseSuccess(200, '查询成功', $tab_list);
    }
    
    //批量操作
    public function BatchOperation($con, $action)
    {
        if (!isset($con['visit_info']) || !$con['visit_info']) {
            return responseError(301, '未选择数据域');
        }
        $visit_info = explode(',', $con['visit_info']);
        $transaction = \Yii::$app->db->beginTransaction();
        foreach ($visit_info as $k => $v) {
            $info = explode('-', $v);
            $check = TabCheck::findOne(['seq' => $info[0], 'tab_id' => $info[1], 'visit_id' => $info[2], 'is_delete' => TabCheck::DELETE_N]);
            if (!$check) {
                $model = self::addNew($info[0], $info[2], $info[1]);
                if ($model === false) {
                    $transaction->rollBack();
                    return responseError(301, '网络错误');
                }
                $check = TabCheck::findOne(['seq' => $info[0], 'tab_id' => $info[1], 'visit_id' => $info[2], 'is_delete' => TabCheck::DELETE_N]);
            }
            //批量修改数据 跳过 签名 锁定 解锁
            switch ($con['subject_check_type']) {
                case 'skip':        //跳过
                    $res = $this->batchChangeOne($con, $check, self::STATUS_SKIPED, AuditTrail::VARB_VALUE_SKIP, "--数据域跳过", '跳过', $action);
                    break;
                case 'sign':        //签名
                    $res = $this->batchChangeOne($con, $check, self::STATUS_SIGNED, AuditTrail::VARB_VALUE_SIGN, "--数据域签名", '签名', $action);
                    break;
                case 'lock':        //锁定
                    $res = $this->batchChangeOne($con, $check, self::STATUS_LOCKED, AuditTrail::VARB_VALUE_LOCK, "--数据域锁定", '锁定', $action);
                    break;
                case 'deblock':        //解锁
                    $res = $this->batchChangeOne($con, $check, self::STATUS_SIGNED, AuditTrail::VARB_VALUE_UNLOCK, "--数据域解锁", '解锁', $action);
                    break;
                case 'sdv':
                    $res = $this->batchChangeTwo($check, $action, 'sdv');
                    break;
                case 'check':
                    $res = $this->batchChangeTwo($check, $action, 'check');
                    break;
                default:
                    return responseSuccess(301, '非法操作');
            }
            if ($res !== true) {
                $transaction->rollBack();
                //    return responseSuccess(301, '网络错误');
                return responseSuccess(301, $res['msg']);
            }
        }
        $transaction->commit();
        return responseSuccess(200, '操作成功');
    }
    
    //批量修改数据 跳过 签名 锁定 解锁
    protected function batchChangeOne($con, $check, $check_status, $type, $str = '', $note = '', $action = '')
    {
        $log = new SubjectActionLog;
        $check->status = $check_status;
        $log->action = $check_status;
        $log->tc_id = $check->id;
        $log->note = $note . $str;
        $res_check = $check->save();
        
        if (!$res_check) {
            return ['code' => 301, 'msg' => '该数据表状态修改失败'];
        }
        //添加日志记录
        $res_log = SubjectActionLog::insertLog($check->id, SubjectActionLog::OBJECT_TABLE, $check->status, $log);
        if ($res_log !== true) {
            return ['code' => 301, 'msg' => '网络错误，日志记录添加失败'];
        }
        //添加稽查痕迹
        $auditTrailBatch = AuditTrail::insertStatusTrail($check, $type, $note);
        if (!$auditTrailBatch) {
            return ['code' => 301, 'msg' => '网络错误，稽查痕迹添加失败'];
        }
        Logs::create($check);
        Logger::write($action, 'TabCheck中id=' . $check->id . $str);
        return true;
    }
    
    
    //批量修改数据 SDV 核查
    protected function batchChangeTwo($check, $action, $type)
    {
        
        if ($type == 'sdv') {
            $check->sdv = TabCheck::STATUS_SDVED;
            $check->sdv_user = getUserId();
            $check->sdv_date = time();
            $res_check = $check->save();
            if (!$res_check) {
                return ['code' => 301, 'msg' => '该数据表状态修改失败'];
            }
            Varb::ValueAdd($check);
            //            $query = VarbSdvCheck::findOne(['tab_check_id' => $check->id, 'seq' => $check->seq, 'sdv' => VarbSdvCheck::STATUS_SDVED]);
            //            if ($query) {
            $auditTrailBatch = AuditTrail::insertTrail($check, 1, 'SDV', AuditTrail::VARB_VALUE_SDV, TabCheck::STATUS_SDVED);
            if (!$auditTrailBatch) {
                return ['code' => 301, 'msg' => '进行数据域SDV时添加稽查痕迹失败'];
            }
            
            //            $varbSdvCheckUpdateAll = VarbSdvCheck::updateAll(['sdv' => TabCheck::STATUS_SDVED], ['tab_check_id' => $check->id, 'seq' => $check->seq, 'sdv' => VarbSdvCheck::STATUS_NOSDV]);
            //            if (!$varbSdvCheckUpdateAll) {
            //                return ['code' => 301, 'msg' => '变量sdv核查操作表记录修改失败'];
            //            }
            //            }
            Logger::write($action, "针对tab_check记录id={$check->id}作SDV操作");
        } else {
            $check->check = TabCheck::STATUS_CHECKED;
            $check->check_user = getUserId();
            $check->check_date = time();
            $res_check = $check->save();
            if (!$res_check) {
                return ['code' => 301, 'msg' => '该数据表状态修改失败'];
            }
            Varb::ValueAdd($check);
            $query = VarbSdvCheck::findOne(['tab_check_id' => $check->id, 'seq' => $check->seq]);
            //            if ($query) {
            $auditTrailBatch = AuditTrail::insertTrail($check, 2, 'DM核查', AuditTrail::VARB_VALUE_CHECK, TabCheck::STATUS_CHECKED);
            if (!$auditTrailBatch) {
                return ['code' => 301, 'msg' => '核查标记时添加稽查痕迹失败'];
            }
            //            $varbSdvCheckUpdateAll = VarbSdvCheck::updateAll(['check' => TabCheck::STATUS_CHECKED], ['tab_check_id' => $check->id, 'seq' => $check->seq, 'check' => VarbSdvCheck::STATUS_UNCHECK]);
            //            if (!$varbSdvCheckUpdateAll) {
            //                return ['code' => 301, 'msg' => '变量核查标记时核查操作表记录修改失败'];
            //            }
            Logger::write($action, "针对tab_check记录id={$check->id}作DM核查操作");
            //            }
        }
        return true;
    }
    
    //符合跳过的数据判断
    protected function batchJudgeSkip($entChecks, $v)
    {
        $is_opt = 1; //默认可以选择
        foreach ($entChecks as $key => $val) {
            if ($v['id'] == $val->tab_id && $val->status != 0) {
                $is_opt = 2;//不可以选择
            }
        }
        return $is_opt;
    }
    
    //符合签名的数据判断
    protected function batchJudgeSign($entChecks, $v)
    {
        $is_opt = 2; //默认不可以选择
        foreach ($entChecks as $key => $val) {
            if ($v['id'] == $val->tab_id && $val->status == self::STATUS_COMPLETED && $val->check == self::STATUS_CHECKED && $val->sdv == self::STATUS_SDVED) {
                $is_opt = 1;
            }
            if ($v['id'] == $val->tab_id && $val->status == self::STATUS_SKIPED && $val->check == self::STATUS_CHECKED && $val->sdv == self::STATUS_SDVED) {
                $is_opt = 1;
            }
        }
        return $is_opt;
    }
    
    //符合锁定的数据判断
    protected function batchJudgeLock($entChecks, $v)
    {
        $is_opt = 2; //默认不可以选择
        foreach ($entChecks as $key => $val) {
            if ($v['id'] == $val->tab_id && $val->status == self::STATUS_SIGNED) {
                $is_opt = 1;
            }
        }
        return $is_opt;
    }
    
    //符合解锁的数据判断
    protected function batchJudgeDeblock($entChecks, $v)
    {
        $is_opt = 2; //默认不可以选择
        foreach ($entChecks as $key => $val) {
            if ($v['id'] == $val->tab_id && $val->status == self::STATUS_LOCKED) {
                $is_opt = 1;
            }
        }
        return $is_opt;
    }
    
    //符合SDV的数据判断
    protected function batchJudgeSdv($entChecks, $v)
    {
        
        $is_opt = 2; //默认不可以选择
        foreach ($entChecks as $key => $val) {
            if ($v['id'] == $val->tab_id) {
                $query = self::hasNewQuery($val->id, $val->seq, $val->visit_id);//变量是否有质疑
                if ($val->status == self::STATUS_SKIPED && $val->sdv != self::STATUS_SDVED) {
                    $is_opt = 1;
                }
                if ($val->status == self::STATUS_COMPLETED && $val->sdv != self::STATUS_SDVED && !$query) {
                    $is_opt = 1;
                }
            }
        }
        return $is_opt;
    }
    
    //符合核查的数据判断
    protected function batchJudgeCheck($entChecks, $v)
    {
        
        $is_opt = 2; //默认不可以选择
        foreach ($entChecks as $key => $val) {
            if ($v['id'] == $val->tab_id) {
                $query = self::hasNewQuery($val->id, $val->seq, $val->visit_id);//变量是否有质疑
                if ($val->status == self::STATUS_SKIPED && $val->check != self::STATUS_CHECKED) {
                    $is_opt = 1;
                }
                if ($val->status == self::STATUS_COMPLETED && $val->check != self::STATUS_CHECKED && !$query) {
                    $is_opt = 1;
                }
            }
        }
        return $is_opt;
    }
}
